package com.sengled.cloud.biz.union.mgt.dao.sys.provider;

import com.sengled.cloud.biz.union.mgt.model.po.Role;
import com.sengled.cloud.biz.union.mgt.model.po.RoleModulePermission;
import com.sengled.cloud.biz.union.mgt.model.request.SaveUserAccountRequest;
import com.sengled.cloud.biz.union.mgt.model.request.ShopAccountListRequest;
import com.sengled.cloud.biz.union.mgt.model.request.UserAccountListRequest;
import com.sengled.cloud.biz.union.mgt.model.vo.ShopQueryParamsBean;

import org.apache.commons.lang.StringUtils;

import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by csolo on 2017/11/8.
 */
public class UserAccountDaoProvider {

   

    public String querySQL(UserAccountListRequest userAccountListRequest) {
        StringBuilder sb = new StringBuilder();

        //sb.append("SELECT sa.id id,sa.account_name accountName,u.user_name userName,r.role_name roleName,CASE WHEN account_status=1 THEN '在线' WHEN account_status=2 THEN '离线' WHEN account_status=3 THEN '锁定' ELSE '失效' END  accountStatus FROM user_account sa,USER u,account_role ar,role r WHERE sa.user_id=u.id AND sa.id=ar.user_account_id AND ar.role_id=r.id ");
        sb.append("SELECT sa.id id,sa.account_name accountName,u.user_name userName,'' roleName,CASE WHEN account_status=1 THEN '在线' WHEN account_status=2 THEN '离线' WHEN account_status=3 THEN '锁定' ELSE '失效' END  accountStatus,sa.enable_mark enableMark FROM user_account sa,user u WHERE sa.user_id=u.id and sa.int001 is null ");
        StringBuilder setStr = new StringBuilder();
   
        
        if(StringUtils.isNotEmpty(userAccountListRequest.getAccountName())){
            setStr.append(" and sa.account_name like CONCAT('%', #{accountName}, '%') ");
        }
        if(StringUtils.isNotEmpty(userAccountListRequest.getUserName())){
            setStr.append(" and u.user_name like CONCAT('%', #{userName}, '%') ");
        }
        
        if(userAccountListRequest.getAccountStatus() != null && userAccountListRequest.getAccountStatus() != 0){
            setStr.append(" and sa.account_status = #{accountStatus}");
        }
        if(userAccountListRequest.getCompanyId() != null){
            setStr.append(" and sa.company_id = #{companyId}");
        }
       
        if(!userAccountListRequest.isSuperMgr()&&userAccountListRequest.getUserId() != null){
        	setStr.append(" and sa.id in(select user_account_id from user_acct_shop_acct where shop_account_id in (select shop_account_id from user_acct_shop_acct where user_account_id =#{userId}))");
        }
        
        setStr.append(" and sa.seal_mark = 0 order by sa.create_date desc" );
        
        if(userAccountListRequest.getStart()!=null&&userAccountListRequest.getPageSize()!=null){
        	setStr.append(" LIMIT #{start},#{pageSize}" );
        }
       
        sb.append(setStr);
        System.out.println("sql : ---------------->"+sb.toString());
        return sb.toString();
    }
    
    public String queryTotalRecords(UserAccountListRequest userAccountListRequest) {
        StringBuilder sb = new StringBuilder();
        //sb.append("SELECT count(sa.id) WHERE sa.user_id=u.id AND sa.id=ar.user_account_id AND ar.role_id=r.id ");
        sb.append("SELECT count(sa.id) FROM user_account sa,user u  WHERE sa.user_id=u.id and sa.int001 is null ");
        
        StringBuilder setStr = new StringBuilder();
   
        if(StringUtils.isNotEmpty(userAccountListRequest.getAccountName())){
            setStr.append(" and sa.account_name like CONCAT('%', #{accountName}, '%') ");
        }
        if(StringUtils.isNotEmpty(userAccountListRequest.getUserName())){
            setStr.append(" and u.user_name like CONCAT('%', #{userName}, '%') ");
        }
        
        if(userAccountListRequest.getAccountStatus() != null && userAccountListRequest.getAccountStatus() != 0){
            setStr.append(" and sa.account_status = #{accountStatus}");
        }
        if(userAccountListRequest.getCompanyId() != null){
            setStr.append(" and sa.company_id = #{companyId}");
        }
        if(!userAccountListRequest.isSuperMgr()&&userAccountListRequest.getUserId() != null){
        	setStr.append(" and sa.id in(select user_account_id from user_acct_shop_acct where shop_account_id in (select shop_account_id from user_acct_shop_acct where user_account_id =#{userId}))");
        }
        
       
        setStr.append(" and sa.seal_mark = 0 order by sa.create_date desc" );
        
        sb.append(setStr);
        System.out.println("sql : ---------------->"+sb.toString());
        return sb.toString();
    }
    

  
    public String batchInsertAccountShop(SaveUserAccountRequest saveUserAccountRequest) {
        List<Long> shopAccountIds = (List<Long>)saveUserAccountRequest.getShopAccountIds();
        StringBuilder sb = new StringBuilder();
        
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String cdate= sf.format(new Date());
    
        sb.append("INSERT INTO user_acct_shop_acct(user_account_id,shop_account_id,operator_id,create_date,seal_mark) VALUES");
          

        shopAccountIds.forEach(shopAccountId -> {
                 
                sb.append("("+saveUserAccountRequest.getId()+","+shopAccountId+","+saveUserAccountRequest.getOperatorId()+",'"+cdate+"',0),");
            });
       
        sb.deleteCharAt(sb.length()-1);
        System.out.println("sql : ---------------->"+sb.toString());
        return sb.toString();
    }
    
//  @Insert("INSERT INTO account_role(user_account_id,role_id,create_date,seal_mark) " +
//  "VALUES(#{userAccount.id},#{userAccount.roleId},#{userAccount.createDate},0)" )
    
    public String batchInsertAccountRole(SaveUserAccountRequest saveUserAccountRequest) {
        List<Long> roleIds = (List<Long>)saveUserAccountRequest.getRoleIds();
        StringBuilder sb = new StringBuilder();
        
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String cdate= sf.format(new Date());
    
        sb.append("INSERT INTO account_role(user_account_id,role_id,operator_id,create_date,seal_mark) VALUES");
          

        roleIds.forEach(roleId -> {
                 
                sb.append("("+saveUserAccountRequest.getId()+","+roleId+","+saveUserAccountRequest.getOperatorId()+",'"+cdate+"',0),");
            });
       
        sb.deleteCharAt(sb.length()-1);
        System.out.println("sql : ---------------->"+sb.toString());
        return sb.toString();
    }
}
